﻿using System;
using System.Data;
using System.Configuration;
using System.Data.OleDb;

/// <summary>
/// Datadeal 的摘要说明
/// </summary>
public class Datadeal
{
	public Datadeal()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}    
     protected  OleDbConnection conn = new OleDbConnection();
    protected  OleDbCommand comm = new OleDbCommand();

    private  void openConnection()
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + XinHaiOA.GlobalUserClass.GlbAcsAddress;
            comm.Connection = conn;
            try
            {
                conn.Open();
            }
            catch (Exception e)
            { throw new Exception(e.Message); }

        }
       
    }//打开数据库
  
    private  void closeConnection()
    {
        if (conn.State == ConnectionState.Open)
        { 
            conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
    }//关闭数据库

    public  bool  ExecuteSQLReturn(string sqlstr)
    {
        bool bl = false;
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            comm.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            bl = true; closeConnection();
        }
        return bl;
    }//执行sql语句

    public  OleDbDataReader dataReader(string sqlstr)
    {
        OleDbDataReader dr = null;
        try
        {
            openConnection();
            comm.CommandText = sqlstr;
            comm.CommandType = CommandType.Text;

            dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            try
            {
                dr.Close();
                closeConnection();
            }
            catch { }
        }
            return dr;
        }//返回指定sql语句的OleDbDataReader对象，使用时请注意关闭这个对象。
        //返回一个结果GetSqlReaderResult
    public  string GetSqlReaderResult(string sqlstr)
    {
        string mResult;
        mResult = "";
        OleDbDataReader dr = null;
        try
        {
            openConnection();
            comm.CommandText = sqlstr;
            comm.CommandType = CommandType.Text;

            dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            if (dr.Read())
            {
                mResult = dr[0].ToString();
                return mResult;
            }
            else
            {
                mResult = "";
                return mResult;
            }
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            closeConnection();
        }
    }
    public  void dataReader(string sqlstr, ref OleDbDataReader dr)
    {
        try
        {
            openConnection();
            comm.CommandText = sqlstr;
            comm.CommandType = CommandType.Text;
            dr=comm.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            try
            {
                if (dr != null && !dr.IsClosed)
                    dr.Close();
            }
            catch
            {
            }
            finally
            {
                closeConnection();
            }
        }
    }//返回指定sql语句的OleDbDataReader对象,使用时请注意关闭

    public  DataSet ExecuteSQLStr(string sqlstr)
    {
        DataSet ds = new DataSet();
        OleDbDataAdapter da = new OleDbDataAdapter();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(ds);
 
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            closeConnection();
        }
        return ds;
    }//返回指定sql语句的dataset

    public  DataSet GetSqlSet(string sqlstr)
    {
        DataSet ds = new DataSet();
        OleDbDataAdapter da = new OleDbDataAdapter();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(ds);

        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            closeConnection();
        }
        return ds;
    }//返回指定sql语句的dataset

    public DataTable dataTable(string sqlstr)
    {
        DataTable dt = new DataTable();
        OleDbDataAdapter da = new OleDbDataAdapter();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(dt);
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            closeConnection();
        }
        return dt;
    }//返回指定sql语句的datatable
    
    public  string sqlResult(string strSQL)
    {
        string mResult;
        try
        {
            openConnection();
            comm.CommandText = strSQL;
            comm.CommandType = CommandType.Text;
            OleDbDataReader  dr=comm.ExecuteReader(CommandBehavior.CloseConnection);
            if (dr.Read())
            {
                mResult = dr[0].ToString();
                return mResult;
            }
            else
            {
                mResult = "";
                return mResult;
            }
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            closeConnection();
        }
    }//返回一个字符串    
    /////////////////////////////////////////////////////////////////////////////////
    /* '======================================
   '类模块名称：CCreateID
   '作用：给单据编号的通用函数
   '作者：蔡华
   '日期：2002-09-03   16：47
   '最新日期 ： 2002-09-03 16：47
   '方式：根据日期pDate，再根据数据库表名mInfo，单据号的列名mID查出当天的最大流水号
   '      CreateID=mStr+pDate+最新流水号（4位）
   '      最新流水号=当天的最大流水号+1
   '返回值：CreateID
   '======================================*/
    public string CreateID(string mID, string mInfo, string mStr, int mLen)
    {
        //1.先 得到固定部分，SD20090217
        //2.找到 表的最大编号  并判断是否为空，表示0001
        //3.找到SerialList表的最大编号（TableName='EpOrderDesc'），
        //4.判断 2者取哪个好
        //5.+1
        //6.补足0
        //7.返回
        int mI;// As Integer
        string mFormat;// As String
        //string mFormat1;// As String
        //string RandKey = "9999";

        int MaxNum;
        string mMaxID;
        string mTmpMaxID;
        string strSQL;
        if (mStr.Trim() == "")
        {
            return "";
        }
        mLen = 4;
        //开始了1 
        mFormat = mStr + DateTime.Now.ToString("yyyyMMdd"); //strSQL.ToString("yyyyMMdd"); //& Format(CDate(pdate), "YYYYMMDD")

        strSQL = "select Max( " + mID + " ) from " + mInfo + " where " + mID + " like '" + mFormat + "'+'____' ";
        mMaxID = GetSqlReaderResult(strSQL);
        if (mMaxID == "")
        {
            mMaxID = "0000";
        }
        else
        {
            mMaxID = mMaxID.Substring(10, mLen);
        }

        strSQL = "select Max(IDSerial) from SerialList Where TableName='" + mInfo + "' And IDSerial like '" + mFormat + "'+'____' ";
        mTmpMaxID = GetSqlReaderResult(strSQL);
        if (mTmpMaxID == "")
        {
            mTmpMaxID = "0000";
        }
        else
        {
            mTmpMaxID = mTmpMaxID.Substring(10, mLen);
        }

        if (Convert.ToInt16(mTmpMaxID) > Convert.ToInt16(mMaxID))
        {
            mMaxID = mTmpMaxID;
        }

        if (mMaxID == "0000")
        {
            mMaxID = mFormat + "0001";
        }
        else
        {
            MaxNum = Convert.ToInt16(mMaxID);
            MaxNum = MaxNum + 1;
            mMaxID = MaxNum.ToString();
            for (mI = 1; mI <= mLen; mI++)
            {
                if (mMaxID.Length < 4)
                {
                    mMaxID = "0" + mMaxID;
                }
            }
            mMaxID = mFormat + mMaxID;
        }
        strSQL = "Insert Into SerialList values('" + mInfo + "','" + mMaxID + "','9999')";
        ExecuteSQLReturn(strSQL);
        return mMaxID;
    }//End Function
    /////////////////////////////////////////////////////////////////////////////////
    /* '======================================
   '类模块名称：CCreateID
   '作用：给单据编号的通用函数
   '作者：蔡华
   '日期：2002-09-03   16：47
   '最新日期 ： 2002-09-03 16：47
   '方式：根据日期pDate，再根据数据库表名mInfo，单据号的列名mID查出当天的最大流水号
   '      CreateID=mStr+pDate+最新流水号（4位）
   '      最新流水号=当天的最大流水号+1
   '返回值：CreateID
   '======================================*/
    public string CreateIDHaveDate(string mID, string mInfo, string mStr, int mLen,DateTime mDate)
    {
        //1.先 得到固定部分，SD20090217
        //2.找到 表的最大编号  并判断是否为空，表示0001
        //3.找到SerialList表的最大编号（TableName='EpOrderDesc'），
        //4.判断 2者取哪个好
        //5.+1
        //6.补足0
        //7.返回
        int mI;// As Integer
        string mFormat;// As String
        //string mFormat1;// As String
        //string RandKey = "9999";

        int MaxNum;
        string mMaxID;
        string mTmpMaxID;
        string strSQL;
        if (mStr.Trim() == "")
        {
            return "";
        }
        mLen = 4;
        //开始了1 
        mFormat = mStr + mDate.ToString("yyyyMMdd"); //strSQL.ToString("yyyyMMdd"); //& Format(CDate(pdate), "YYYYMMDD")

        strSQL = "select Max( " + mID + " ) from " + mInfo + " where " + mID + " like '" + mFormat + "'+'____' ";
        mMaxID = GetSqlReaderResult(strSQL);
        if (mMaxID == "")
        {
            mMaxID = "0000";
        }
        else
        {
            mMaxID = mMaxID.Substring(10, mLen);
        }

        strSQL = "select Max(IDSerial) from SerialList Where TableName='" + mInfo + "' And IDSerial like '" + mFormat + "'+'____' ";
        mTmpMaxID = GetSqlReaderResult(strSQL);
        if (mTmpMaxID == "")
        {
            mTmpMaxID = "0000";
        }
        else
        {
            mTmpMaxID = mTmpMaxID.Substring(10, mLen);
        }

        if (Convert.ToInt16(mTmpMaxID) > Convert.ToInt16(mMaxID))
        {
            mMaxID = mTmpMaxID;
        }

        if (mMaxID == "0000")
        {
            mMaxID = mFormat + "0001";
        }
        else
        {
            MaxNum = Convert.ToInt16(mMaxID);
            MaxNum = MaxNum + 1;
            mMaxID = MaxNum.ToString();
            for (mI = 1; mI <= mLen; mI++)
            {
                if (mMaxID.Length < 4)
                {
                    mMaxID = "0" + mMaxID;
                }
            }
            mMaxID = mFormat + mMaxID;
        }
        strSQL = "Insert Into SerialList values('" + mInfo + "','" + mMaxID + "','9999')";
        ExecuteSQLReturn(strSQL);
        return mMaxID;
    }//End Function
}
